package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Activity;
import com.hnl.util.FormatUtil;

public class DBActivityDAO implements ActivityDAO {
	public static final Logger LOG = LoggerFactory
			.getLogger(DBActivityDAO.class);

	private PreparedStatement saveActivityStmt;

	private PreparedStatement loadActivityStmt;
	private PreparedStatement loadOneTalentActivityStmt;
	private PreparedStatement updateActivityStmt;

	// private PreparedStatement delActivityStmt;

	protected DBActivityDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public Activity saveActivity(Activity activity) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			saveActivityStmt = conn
					.prepareStatement(
							"INSERT INTO activity (talent_ID, activity_type, isdelete, create_date, create_user, modify_date, modify_user, description) "
									+ "VALUES (?,?,?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveActivityStmt.setInt(1, activity.getTalentID());
			saveActivityStmt.setInt(2, activity.getActivityType());
			saveActivityStmt.setBoolean(3, activity.getIsdelete());
			saveActivityStmt.setString(4,
					FormatUtil.getXmlDate(activity.getCreateDate()));
			saveActivityStmt.setInt(5, activity.getCreateUser());
			saveActivityStmt.setString(6,
					FormatUtil.getXmlDate(activity.getModifyDate()));
			saveActivityStmt.setInt(7, activity.getModifyUser());
			saveActivityStmt.setString(8, activity.getDescription());
			saveActivityStmt.executeUpdate();

			ResultSet rs = saveActivityStmt.getGeneratedKeys();
			if (rs.next()) {
				activity.setActivityID(rs.getInt(1));
			}
			rs.close();
			saveActivityStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving Activity.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return activity;
	}

	public Activity loadActivity(Integer activityID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Activity act = null;
		try {
			loadActivityStmt = conn
					.prepareStatement("SELECT activity_ID, talent_ID, activity_type, isdelete, create_date, create_user, modify_date, modify_user, description FROM activity WHERE activity_ID = ?");
			loadActivityStmt.setInt(1, activityID);
			ResultSet rs = loadActivityStmt.executeQuery();

			if (rs.next()) {
				act = new Activity();
				act.setActivityID(rs.getInt("activity_ID"));
				act.setTalentID(rs.getInt("talent_ID"));
				act.setActivityType(rs.getInt("activity_type"));
				act.setIsdelete(rs.getBoolean("isdelete"));
				act.setCreateDate(rs.getDate("create_date"));
				act.setCreateUser(rs.getInt("create_user"));
				act.setModifyDate(rs.getDate("modify_date"));
				act.setModifyUser(rs.getInt("modify_user"));
				act.setDescription(rs.getString("description"));
			}
			rs.close();
			loadActivityStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Activity.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return act;
	}

	public Collection<Activity> loadOneTalentActivity(Integer talentID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<Activity> actCollection = new Vector<Activity>();
		try {
			loadOneTalentActivityStmt = conn
					.prepareStatement("SELECT activity_ID, talent_ID, activity_type, isdelete, create_date, create_user, modify_date, modify_user, description FROM activity WHERE talent_ID = ? AND isdelete=false ORDER BY modify_date DESC");
			loadOneTalentActivityStmt.setInt(1, talentID);
			ResultSet rs = loadOneTalentActivityStmt.executeQuery();
			while (rs.next()) {
				Activity act = new Activity();
				act.setActivityID(rs.getInt("activity_ID"));
				act.setTalentID(rs.getInt("talent_ID"));
				act.setActivityType(rs.getInt("activity_type"));
				act.setIsdelete(rs.getBoolean("isdelete"));
				act.setCreateDate(rs.getDate("create_date"));
				act.setCreateUser(rs.getInt("create_user"));
				act.setModifyDate(rs.getDate("modify_date"));
				act.setModifyUser(rs.getInt("modify_user"));
				act.setDescription(rs.getString("description"));
				actCollection.add(act);
			}
			rs.close();
			loadOneTalentActivityStmt.close();
		} catch (SQLException e) {
			LOG.error("Error load one talent activity.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return actCollection;
	}

	public void updateActivity(Activity activity) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			updateActivityStmt = conn
					.prepareStatement("UPDATE activity SET talent_ID=?, activity_type=?, isdelete=?, modify_date=?, modify_user=?, description=? WHERE activity_ID=?");

			updateActivityStmt.setInt(1, activity.getTalentID());
			updateActivityStmt.setInt(2, activity.getActivityType());
			updateActivityStmt.setBoolean(3, activity.getIsdelete());
			updateActivityStmt.setString(4,
					FormatUtil.getXmlDate(activity.getModifyDate()));
			updateActivityStmt.setInt(5, activity.getModifyUser());
			updateActivityStmt.setString(6, activity.getDescription());
			updateActivityStmt.setInt(7, activity.getActivityID());
			updateActivityStmt.executeUpdate();
			updateActivityStmt.close();
		} catch (SQLException e) {
			LOG.error("Error update Activity.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public void delActivity(Integer activityID) {
		Activity act = loadActivity(activityID);
		act.setIsdelete(true);
		updateActivity(act);
	}

}
